-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint: 向视图中插入数据，触发trigger，instead of

--step1:建表;expect:成功
drop table if exists table_src_0004_1 cascade;
drop table if exists table_des_0004_2 cascade;
create table table_src_0004_1(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
create table table_des_0004_2(id1 int, id2 int, id3 int) with (storage_type=ustore)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
--step2:创建视图，创建函数，创建触发器;expect:成功
drop view if exists test_trigger_src_view_0004;
create view test_trigger_src_view_0004 as select * from table_src_0004_1;
create or replace function tri_insert_func_0004_1() returns trigger as
$$
declare
begin
insert into table_des_0004_2 select * from test_trigger_src_view_0004;
return new;
end
$$ language plpgsql;
/
drop trigger if exists insert_trigger_0004_1 on test_trigger_src_view_0004 cascade;
create trigger insert_trigger_0004_1 before insert on test_trigger_src_view_0004
        for each statement
                execute procedure tri_insert_func_0004_1();
/
create or replace function tri_insert_func_0004_2()
returns trigger as $$
begin
insert into table_src_0004_1
values(new.id1,new.id2,new.id3);
return new;
end;
$$language plpgsql;
/
create trigger insert_trigger_0004_2
instead of insert on test_trigger_src_view_0004
for each row
execute procedure tri_insert_func_0004_2();
/
--step3:向视图中插入数据，触发trigger，expect:成功
select * from table_des_0004_2;
insert into test_trigger_src_view_0004 values(1,1,1),(2,2,2);
select * from table_des_0004_2;

--step3:环境清理;expect:成功
drop trigger insert_trigger_0004_1 on test_trigger_src_view_0004 cascade;
drop trigger insert_trigger_0004_2 on test_trigger_src_view_0004 cascade;
drop view test_trigger_src_view_0004;
drop table table_src_0004_1 cascade;
drop table table_des_0004_2 cascade;
drop function tri_insert_func_0004_1;
drop function tri_insert_func_0004_2;